package br.com.iqt;

import br.com.iqt.zql.ZFromItem;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.StringTokenizer;
import java.util.Vector;

/**
 *
 * @author Arlino
 */
public class DriverStatisticsOracle implements DriverStatistics {
    private Dbms dbms;
    private String url;

    DriverStatisticsOracle(Dbms dbms) throws ClassNotFoundException {
        this.dbms = dbms;
        Class.forName(Dbms.driverPostgresql);
        this.url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":hmg";
    }

    @Override
    public boolean haveIndex(Vector tables, String column) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();
        ResultSet rs;
        boolean result = false;
        for (Object object : tables) {
            ZFromItem tb = (ZFromItem) object;
            String table = tb.getTable();
            //String scheme = tb.getSchema();

            //if (scheme == null || scheme.isEmpty()) {
             //   scheme = "dbo";
            //}
            String sql = "select * from user_ind_columns where upper(table_name) = upper('" + table + "') and "
                    + "upper(column_name) = upper('" + column + "') and upper(index_name) IN (select "
                    + "upper(index_name) from user_indexes where upper(table_name) = upper('" + table + "'))";
            rs = stm.executeQuery(sql);
            result = rs.next();

            if (result) {
                break;
            }
        }
    
        stm.close();
        connection.close();
        return result;
    }

    @Override
    public boolean isPrimaryKey(Vector tables, String column) throws SQLException {
        String user = this.dbms.getUser();
        String password = this.dbms.getPassword();
        Connection connection = (Connection) DriverManager.getConnection(this.url, user, password);
        
        Statement stm = connection.createStatement();;
        ResultSet rs = null;
        boolean result = false;
        for (Object object : tables) {
            ZFromItem fi = (ZFromItem) object;
            String table = fi.getTable();
            //String scheme = fi.getSchema();
            //if(scheme == null || scheme.equals(""))
            //    scheme = "dbo";

            String sql = "select * from user_cons_columns where upper(table_name) = upper('" + table + "') and "
                    + "upper(column_name) = upper('" + column + "') AND upper(constraint_name)  IN "
                    + "(select upper(constraint_name) from user_constraints where upper(table_name) = "
                    + "upper('" + table + "') and constraint_type = 'P')";
            rs = stm.executeQuery(sql);
            result = rs.next();

            if (result) 
                break;
        }
        stm.close();
        connection.close();
        return result;
    }

    @Override
    public ZFromItem getTableWithPrimaryKey(Vector tables, String column) throws SQLException {
        String user = this.dbms.getUser();
        String password = this.dbms.getPassword();
        Connection connection = (Connection) DriverManager.getConnection(this.url, user, password);

        Statement stm = connection.createStatement();
        ResultSet rs = null;
        ZFromItem tableWithPrimaryKey = null;
        for (Object object : tables) {
            ZFromItem tb = (ZFromItem) object;
            String table = tb.getTable();
            //String scheme = tb.getSchema();
            //if(scheme == null || scheme.equals(""))
            //    scheme = "public";

            String sql = "select * from user_cons_columns where upper(table_name) = upper('" + table + "') and "
                    + "upper(column_name) = upper('" + column + "') AND upper(constraint_name)  IN "
                    + "(select upper(constraint_name) from user_constraints where upper(table_name) = "
                    + "upper('" + table + "') and constraint_type = 'P')";
            rs = stm.executeQuery(sql);

            if (rs.next()) {
                tableWithPrimaryKey = tb;
                break;
            }
        }
        stm.close();
        connection.close();
        return tableWithPrimaryKey;
    }

    @Override
    public boolean isForingkeyFromTable1InTabel2(ZFromItem table, String column, Vector from) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();

        boolean result = false;
        for (Object obj : from) {
            ZFromItem tb = (ZFromItem) obj;

            /*String schema1 = table.getSchema();
            if (schema1 == null || schema1.equals("")) {
                schema1 = "public";
            }
            

            String schema2 = tb.getSchema();
            if (schema2 == null || schema2.equals("")) {
                schema2 = "public";
            }*/
            String table1 = table.getTable();
            String table2 = tb.getTable();

            if (!table1.equalsIgnoreCase(table2) /*|| schema1.equalsIgnoreCase(schema2)*/) {
                String sql = "select * from "
                        + "user_cons_columns where constraint_name = (select constraint_name from user_"
                        + "constraints where table_name = 'LINEITEM' and constraint_type = 'R' AND r_"
                        + "constraint_name = (select constraint_name from user_cons_columns where "
                        + "upper(table_name) = upper('" + table1 + "') and upper(column_name) = "
                        + "upper('" + column + "') AND upper(constraint_name)  = (select upper("
                        + "constraint_name) from user_constraints where upper(table_name) = upper('"
                        + table1 + "') and constraint_type = 'P')))";
                ResultSet rs = stm.executeQuery(sql);
                result = rs.next();
                
                if (result) {
                    break;
                }
            }
        }
        stm.close();
        connection.close();
        return result;
    }

    @Override
    public ZFromItem getTableWithForingkeyFromTable1InTabel2(ZFromItem table, String column, Vector from) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();
        
        ZFromItem tableWithForingkey = null;
        for (Object obj : from) {
            ZFromItem tb = (ZFromItem) obj;

            /*tring schema1 = table.getSchema();
            if (schema1 == null) {
                schema1 = "";
            }

            String schema2 = tb.getSchema();
            if (schema2 == null) {
                schema1 = "";
            }*/
            String table1 = table.getTable();
            String table2 = tb.getTable();

            if (!table1.equalsIgnoreCase(table2) /*|| schema1.equalsIgnoreCase(schema2)*/) {
                String sql = "select * from "
                        + "user_cons_columns where constraint_name = (select constraint_name from user_"
                        + "constraints where table_name = 'LINEITEM' and constraint_type = 'R' AND r_"
                        + "constraint_name = (select constraint_name from user_cons_columns where "
                        + "upper(table_name) = upper('" + table1 + "') and upper(column_name) = "
                        + "upper('" + column + "') AND upper(constraint_name)  = (select upper("
                        + "constraint_name) from user_constraints where upper(table_name) = upper('" 
                        + table1 + "') and constraint_type = 'P')))";
                ResultSet rs = stm.executeQuery(sql);

                if (rs.next()) {
                    tableWithForingkey = tb;
                }
            }
        }
        stm.close();
        connection.close();
        return tableWithForingkey;
    }

    @Override
    public ArrayList getExecutionPlan(String sql) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement statement = connection.createStatement();
      
        sql = "select operation, id from v$sql_plan sp, v$sql s where "
                + "sp.sql_id = s.sql_id and sql_text = '" + sql + "'";
        
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println(sql);
        ArrayList list = new ArrayList();
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
            list.add(resultSet.getString(1));
        }
        
        statement.close();
        connection.close();
        return list;
    }

    @Override
    public String getRealRuntime(String sql) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();

        long finalTime = 0;
        StringTokenizer stringTokenizer = new StringTokenizer(sql, ";");
        while (stringTokenizer.hasMoreElements()) {
            String sqlAux = stringTokenizer.nextToken();
            String executionType;
            if (!sqlAux.toUpperCase().trim().startsWith("SELECT") || sqlAux.trim().toUpperCase().contains(" INTO ")) {
                executionType = "executeUpdate";
            } else {
                executionType = "executeQuery";
            }

            long startTime = System.currentTimeMillis();
            if (executionType.equals("executeUpdate")) {
                stm.executeUpdate(sqlAux);
            } else {
                stm.executeQuery(sqlAux);
            }
            long endTime = System.currentTimeMillis();

            finalTime += endTime - startTime;
        }
        stm.close();
        connection.close();
        return (Long.toString(finalTime) + " ms");
    }
}
